primary key

Primary Key On Create Table -:



Creates a PRIMARY KEY on the "ID" column when the "Persons" table is created:

Syntax-:


• CREATE TABLE Persons (ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255),Age int,PRIMARY KEY (ID));
• CREATE TABLE Persons ( ID int NOT NULL PRIMARY KEY,LastName varchar(255) NOT NULL,FirstName varchar(255),Age int);
• CREATE TABLE Persons (ID int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Age int,CONSTRAINT PK_Persons PRIMARY KEY (ID,LastName));




Example -:



Multiple Columns Primary Key -:

Example -:



✓ Primary Key On Alter Table -:

To create a PRIMARY KEY constraint on the "ID" column when the table is already created

Syntax-:

• ALTER TABLE Persons ADD PRIMARY KEY (ID);

• ALTER TABLE Persons ADD CONSTRAINT PK_table PRIMARY KEY (ID,LastName);

• ALTER TABLE Persons DROP PRIMARY KEY;

• ALTER TABLE Persons DROP CONSTRAINT PK_ Persons;

Example -:





Drop Primary Key Example -:




foriegn key

Foreign Key Constraint -:



The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.
The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.

Syntax-:

• CREATE TABLE Orders (OrderID int NOT NULL,OrderNumber int NOT NULL, PersonID int,PRIMARY KEY (OrderID),FOREIGN KEY (PersonID) REFERENCES Persons(PersonID));

• CREATE TABLE Orders (OrderID int NOT NULL,OrderNumber int NOT NULL, PersonID int,PRIMARY KEY (OrderID),CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)REFERENCES Persons(PersonID));

Example -:



✓ Foreign Key On Alter Table -: To create a FOREIGN KEY constraint on the "PersonID" column when the "Orders" table is already created Syntax-:

• ALTER TABLE Orders ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

• ALTER TABLE Orders ADD CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

Example -:



✓ Drop A Foreign Key Constraint -:

Syntax-:

• ALTER TABLE Orders DROP FOREIGN KEY FK_PersonOrder;

• ALTER TABLE Orders DROP CONSTRAINT FK_PersonOrder;

Example -: